package it.cinema.mobile.biz;


import it.cinema.mobile.data.Parameter;
import it.cinema.mobile.data.Film;
import it.cinema.mobile.data.Persona;
import it.cinema.mobile.data.Proiezione;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.StringTokenizer;
import java.util.logging.Logger;

import javax.annotation.Resource;
import javax.faces.bean.ApplicationScoped;
import javax.faces.bean.ManagedBean;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

@ManagedBean
@ApplicationScoped
public class FilmDAO {
	private static final Logger log = Logger.getLogger(it.cinema.mobile.data.Film.class.getName());

	@Resource(name=("jdbc/dbcinema"))
	private DataSource ds;
	
	public List<Film> findFilm(Parameter parameter){
		ArrayList<Film> films = null;
		try {
			if(ds==null){
				Context ctx = new InitialContext();
				ds = (DataSource) ctx.lookup("java:comp/env/jdbc/dbcinema");
			}
			
			Connection con = ds.getConnection();
			Statement statement = con.createStatement();
			String query = "select * from film f ";

			String filmIDs = parameter.getFilms();
			ArrayList<String> ids = new ArrayList<String>();
			if (filmIDs != null && !filmIDs.isEmpty()){
				StringTokenizer st = new StringTokenizer(filmIDs,",");
				query = query + "where ";
				while (st.hasMoreElements()) {
					String token = st.nextElement().toString();
					ids.add(token);
					query = query + "f.idfilm = '"+token+"'";
					if(st.hasMoreElements())
						query = query + " or ";
				}
			}
			
			ArrayList<String> score = new ArrayList<String>();
			String scoreValue = parameter.getScore();
			if (scoreValue != null && !scoreValue.isEmpty()){
				StringTokenizer st = new StringTokenizer(scoreValue,",");
				while (st.hasMoreElements())
					score.add(st.nextElement().toString());
			}
			
			log.info("Query : "+query);

			HashMap<String,String> scoreMap = new HashMap<String,String>();
			if (scoreValue != null && !scoreValue.isEmpty())
				for(int i=0; i<ids.size(); i++)
					scoreMap.put(ids.get(i), score.get(i));
			
			ResultSet resultSet = statement.executeQuery(query);
			films = new ArrayList<Film>();
			Film f = null;
			ArrayList<Proiezione> proiezioni = new ArrayList<Proiezione>();
			ArrayList<Persona> persone = new ArrayList<Persona>();
			for (; resultSet.next(); films.add(f)){
				if(scoreValue!=null){
					f = new Film(resultSet.getInt("idfilm"),
						resultSet.getString("titolo"),
						resultSet.getString("desc_breve"),
						resultSet.getString("trama"),
						resultSet.getString("locandina"), 
						resultSet.getString("ind_gradimento"),
						scoreMap.get(new Integer(resultSet.getInt("idfilm")).toString()));
					f.setScore_label("Score :");
				}
				else
					f = new Film(resultSet.getInt("idfilm"),
							resultSet.getString("titolo"),
							resultSet.getString("desc_breve"),
							resultSet.getString("trama"),
							resultSet.getString("locandina"), 
							resultSet.getString("ind_gradimento"),
							"");

				proiezioni = getProiezioni(f.getIdFilm(), parameter.getCinema());
				persone = getPersone(f.getIdFilm());
				f.setProiezioni(proiezioni);
				f.setPersona(persone);
			}
			
			resultSet.close();
			statement.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return films;
	}
	
	private ArrayList<Proiezione> getProiezioni(int idFilm, String idCinema){
		ArrayList<Proiezione> proiezioni = new ArrayList<Proiezione>();
		String query = "select * from sala_proietta_film where sala_cinema_idcinema = ? and film_idfilm = ?";
		Connection con;
		try {
			con = ds.getConnection();
			PreparedStatement statement = con.prepareStatement(query);
			statement.setInt(1, new Integer(idCinema).intValue());
			statement.setInt(2, idFilm);
			ResultSet rs = statement.executeQuery();
			Proiezione p = null;
			for (; rs.next(); proiezioni.add(p)){
				p = new Proiezione();
				p.setOra(rs.getString("ora"));
				p.setPrezzo(rs.getString("prezzo"));
				p.setTreD(rs.getBoolean("3d"));
			}
			rs.close();
			statement.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return proiezioni;
	}
	
	private ArrayList<Persona> getPersone(int idFilm){
		ArrayList<Persona> persone = new ArrayList<Persona>();
		String query = "select p.nome_cognome, p.ruolo from persona p, film_has_persona fhp where p.idpersona = fhp.persona_idpersona and fhp.film_idfilm = ? ";
		Connection con;
		try {
			con = ds.getConnection();
			PreparedStatement statement = con.prepareStatement(query);
			statement.setInt(1, idFilm);
			ResultSet rs = statement.executeQuery();
			Persona p = null;
			for (; rs.next(); persone.add(p)){
				p = new Persona();
				p.setNome_cognome(rs.getString("nome_cognome"));
				p.setRuolo(rs.getString("ruolo"));
			}
			rs.close();
			statement.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return persone;
	}
}